Cloud SQL basics

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform.

Install and Import the dependencies

!pip install pymysql \ import pymysql

Create Cloud SQL Instance

Unlike Bigquery and GCS, Cloud SQL is not a managed service and requires creation from the user. The user will be billed only for the resources used.
Follow the steps provided here to create a new Cloud SQL instance in your project.
Once the Cloud SQL instance starts running do following from GCP Console:

Create a new user, remember the username and password. \
Create a new database. \
Save the Instance Connection name from Instance description. \

Download SQL proxy

The Cloud SQL Proxy provides secure access to your Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL.



In [ ]:

    
!wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
!chmod +x cloud_sql_proxy

Invoke Cloud SQL proxy without using any authentication



In [ ]:

    
!./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 &

Invoke Cloud SQL proxy using any Service account JSON



In [ ]:

    
!./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 \
                  -credential_file=<PATH_TO_KEY_FILE> &

Initialize a connection

To use the Cloud SQL client library, start by initializing a pymysql connection. The connection is used to establish a bridge between your machine running JupyterLab and Cloud SQL instance

Run the following to create a connection:



In [ ]:

    
import pymysql

# TODO(developer): 
# Change USERNAME and PASSWORD to the user and password created on Cloud SQL instance
# Set DB to the name of the database to be connected to

connection = pymysql.connect(host='127.0.0.1',
                             user='USERNAME',
                             password='PASSWORD',
                             db='DB')

Create a cursor for this connection to interact with the database.



In [ ]:

    
mycursor = connection.cursor()

Create a new table



In [ ]:

    
mycursor.execute("create table EMPLOYEE ( \
   EMP_ID bigint not null, \
   EMP_NAME varchar(50) not null, \
   EMP_NO varchar(20) not null, \
   HIRE_DATE date not null, \
   IMAGE longblob, \
   JOB varchar(30) not null, \
   SALARY float not null, \
   DEPT_ID integer not null, \
   MNG_ID bigint, \
   primary key (EMP_ID), \
   unique (EMP_NO) \
);")
mycursor.fetchall()
print(mycursor.description)

Insert value in the table



In [ ]:

    
mycursor.execute("insert into EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID) \
values (7839, 'KING', 'E7839', Str_To_Date('17-11-1981', '%d-%m-%Y'), 'PRESIDENT', 5000, 10, null);")

Read value from the table



In [ ]:

    
mycursor.execute("SELECT * FROM EMPLOYEE")
mycursor.fetchall()

Do other SQL operations using cursor

The cursor provides the execute() method to execute any SQL query
Cursor also provides fetchone() and fetchall() methods to display either just one row or all the rows from the result of the query, if any



In [ ]:

    
#Execute a SQL command
mycursor.execute(SQL_COMMAND)
# Display all the rows from output of the previous execution using fetchall()
mycursor.fetchall()
# Display only one row from output of the previous execution using fetchall()
mycursor.fetchone()